﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using ET.DBCommon.Model;
using MySql.Data.MySqlClient;

namespace ET.DBCommon.DAL
{
    public class UserInfoDAL
    {
        public static UserInfoModel SelectUserInfo(int ID)
        {
            string sql = "select * from userinfo where Id=" + ID;
            DataSet ds = DbHelper.ExecuteDataset(sql);
            UserInfoModel info = null;
            if (ds != null && ds.Tables[0].Rows.Count > 0)
            {
                DataTable dt = ds.Tables[0];
                if (dt.Rows.Count > 0)
                    info = GetUserInfoModel(dt.Rows[0]);
            }
            return info;
        }
      

        public static UserInfoModel SelectUserInfo(string userNumber)
        {
            string sql = "select * from userinfo where UserNumber='" + userNumber + "'";
            DataSet ds = DbHelper.ExecuteDataset(sql);
            UserInfoModel info = null;
            if (ds != null && ds.Tables[0].Rows.Count > 0)
            {
                DataTable dt = ds.Tables[0];
                if (dt.Rows.Count > 0)
                    info = GetUserInfoModel(dt.Rows[0]);
            }
            return info;
        }

        public static List<UserInfoModel> SelectUserList()
        {
            string sql = "select * from userinfo";
            DataSet ds = DbHelper.ExecuteDataset(sql);
            List<UserInfoModel> lst = new List<UserInfoModel>();
            if (ds != null && ds.Tables[0].Rows.Count > 0)
            {
                DataTable dt = ds.Tables[0];
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    lst.Add(GetUserInfoModel(dt.Rows[i]));
                }
            }
            return lst;
        }


        public static bool DeleteUser(int ID)
        {
            string sql = "delete from userinfo WHERE Id = @ID";
            MySqlParameter[] parms = {
                new MySqlParameter("@ID", ID)
            };
            return DbHelper.ExecuteNonQuery(sql, parms) > 0;
        }

        private static UserInfoModel GetUserInfoModel(DataRow dr)
        {
            UserInfoModel info = new UserInfoModel();
            info.Id = Convert.ToInt32(dr["Id"].ToString());
            info.UserName = dr["UserName"].ToString();
            if (!(dr["Gender"] == DBNull.Value))
                info.Gender = dr["Gender"].ToString();
            info.UserNumber = dr["UserNumber"].ToString();
            if (!(dr["Age"] == DBNull.Value))
                info.Age = dr["Age"].ToString();
            if (!(dr["MobilePhone"] == DBNull.Value))
                info.MobilePhone = dr["MobilePhone"].ToString();
            if (!(dr["IdCard"] == DBNull.Value))
                info.IdCard = dr["IdCard"].ToString();
            if (!(dr["Group"] == DBNull.Value))
                info.Group = dr["Group"].ToString();
            return info;
        }

        public static bool InsertUser(out int newId, UserInfoModel user)
        {
            string sql = "insert into userinfo (UserName,Gender,UserNumber,Age,MobilePhone,IdCard,`Group`) values (@UserName,@Gender,@UserNumber,@Age,@MobilePhone,@IdCard,@Group)";
            MySqlParameter[] parms = {
                new MySqlParameter("@UserName", user.UserName),
                new MySqlParameter("@Gender", user.Gender),
                new MySqlParameter("@UserNumber", user.UserNumber),
                new MySqlParameter("@Age", user.Age),
                new MySqlParameter("@MobilePhone", user.MobilePhone),
                new MySqlParameter("@IdCard", user.IdCard),
                new MySqlParameter("@Group", user.Group)

            };

            return DbHelper.ExecuteNonQuery(out newId, sql, parms) > 0;
        }

        public static bool UpdateUser(UserInfoModel user)
        {
            string sql = "update userinfo set UserName = @UserName,Gender = @Gender,UserNumber = @UserNumber,Age = @Age,MobilePhone = @MobilePhone,IdCard = @IdCard,Group = @Group WHERE Id = @Id";
            MySqlParameter[] parms = {
                 new MySqlParameter("@UserName", user.UserName),
                new MySqlParameter("@Gender", user.Gender),
                new MySqlParameter("@UserNumber", user.UserNumber),
                new MySqlParameter("@Age", user.Age),
                new MySqlParameter("@MobilePhone", user.MobilePhone),
                new MySqlParameter("@IdCard", user.IdCard),
                new MySqlParameter("@Group", user.Group),
                new MySqlParameter("@Id", user.Id)
            };
            return DbHelper.ExecuteNonQuery(sql, parms) > 0;
        }

        public static bool UpdateFaceRegister(UserInfoModel user)
        {
            string sql = "update userinfo set FaceRegister = @FaceRegister WHERE Id = @Id";
            MySqlParameter[] parms = {
                new MySqlParameter("@FaceRegister", user.FaceRegister),
                new MySqlParameter("@Id", user.Id)
            };
            return DbHelper.ExecuteNonQuery(sql, parms) > 0;
        }
        
    }
}
